﻿using ChurchDiary.Common;
using ChurchDiary.DAL.Contract;
using ChurchDiary.DAL.DAL;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using ChurchDiary.TableEntity;

namespace ChurchDiary.DAL.Methods
{
    public class DAL_ChurchDetails : DataAccess, IDALChurch
    {
        public List<EntityChurch> GetChurch(int PageNo, string SearchKey, string Sort, string SortMode, int PerPage,Guid PartitionId,Guid DesignationId)
        {
            List<EntityChurch> Churches = new List<EntityChurch>();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_Church]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.LIST);
                    SQLServer.AddInParameter(dbCommand, "@PageNumber", DbType.Int32, PageNo);
                    SQLServer.AddInParameter(dbCommand, "@SearchKey", DbType.String, SearchKey);
                    SQLServer.AddInParameter(dbCommand, "@Sort", DbType.String, Sort);
                    SQLServer.AddInParameter(dbCommand, "@SortMode", DbType.String, SortMode);
                    SQLServer.AddInParameter(dbCommand, "@RowsPerPage", DbType.Int32, PerPage);
                    if (PartitionId.ToString() != "00000000-0000-0000-0000-000000000000")
                    {
                        SQLServer.AddInParameter(dbCommand, "@PartitionId", DbType.Guid, PartitionId);
                    }
                    if (DesignationId.ToString() != "00000000-0000-0000-0000-000000000000")
                    {
                        SQLServer.AddInParameter(dbCommand, "@DesignationId", DbType.Guid, DesignationId);
                    }
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return Churches;
                            }
                            while (OReader.Read())
                            {
                                EntityChurch Church = new EntityChurch();
                                Church.ChurchId = (Guid)OReader["ChurchId"];
                                Church.ChurchNo = (Int32)OReader["ChurchNo"];
                                Church.ChurchName = OReader["ChurchName"].ToString();
                                Church.IsLoginCreated = (bool)OReader["IsLoginCreated"];
                                Church.Email = OReader["Email"].ToString();
                                Church.ContactNo = OReader["ContactNo"].ToString();
                                Church.GradeId = (Guid)OReader["GradeId"];
                                Church.PartitionId = (Guid)OReader["PartitionId"];
                                Church.DesignationId = (Guid)OReader["DesignationId"];
                                Church.Image = CommonFunction.GetOrginalImagePath(OReader["Image"].ToString(), IMAGEPATH.CHURCHIMAGE);
                                Church.OrganizationId = (Int32)OReader["OrganizationId"];
                                Church.TotalRecords = (Int32)OReader["TotalRecords"];
                                Church.ChurchContact = new EntityChurchContact();
                                Church.ChurchContact.CountryId = (Guid)OReader["CountryId"];
                                Church.ChurchContact.StateId = (Guid)OReader["StateId"];
                                Church.ChurchContact.Addressline1 = OReader["Addressline1"].ToString();
                                Church.ChurchContact.Addressline2 = OReader["Addressline2"].ToString();
                                Church.ChurchContact.Pincode = OReader["Pincode"].ToString();
                                Church.OrganizationPartition = new EnitityOrganizationPartition();
                                Church.OrganizationPartition.PartitionName = OReader["PartitionName"].ToString();
                                Church.OrganizationPartition.PartitionAbrivation = OReader["PartitionAbrivation"].ToString();
                                Church.OrganizationPartition.OrganizationLevel = (Int32)OReader["OrganizationLevel"];
                                Church.OrganizationPartition.ParentPartitionId = (Guid)OReader["ParentPartitionId"];
                                Church.Designation = new EntityChurchDesignation();
                                Church.Designation.DesignationAbrivation = OReader["DesignationAbrivation"].ToString();
                                Church.Designation.DesignationName = OReader["DesignationName"].ToString();
                                Churches.Add(Church);
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return Churches;
        }
        public List<EntityChurch> PrintChurch(string SearchKey, string Sort, string SortMode, Guid PartitionId, Guid DesignationId)
        {
            List<EntityChurch> Churches = new List<EntityChurch>();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_RPT_Church]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, "REPORT");
                    SQLServer.AddInParameter(dbCommand, "@SearchKey", DbType.String, SearchKey);
                    SQLServer.AddInParameter(dbCommand, "@Sort", DbType.String, Sort);
                    SQLServer.AddInParameter(dbCommand, "@SortMode", DbType.String, SortMode);
                    if (PartitionId.ToString() != "00000000-0000-0000-0000-000000000000")
                    {
                        SQLServer.AddInParameter(dbCommand, "@PartitionId", DbType.Guid, PartitionId);
                    }
                    if (DesignationId.ToString() != "00000000-0000-0000-0000-000000000000")
                    {
                        SQLServer.AddInParameter(dbCommand, "@DesignationId", DbType.Guid, DesignationId);
                    }
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return Churches;
                            }
                            while (OReader.Read())
                            {
                                EntityChurch Church = new EntityChurch();
                                Church.ChurchId = (Guid)OReader["ChurchId"];
                                Church.ChurchNo = (Int32)OReader["ChurchNo"];
                                Church.ChurchName = OReader["ChurchName"].ToString();
                                Church.IsLoginCreated = (bool)OReader["IsLoginCreated"];
                                Church.Email = OReader["Email"].ToString();
                                Church.ContactNo = OReader["ContactNo"].ToString();
                                Church.GradeId = (Guid)OReader["GradeId"];
                                Church.PartitionId = (Guid)OReader["PartitionId"];
                                Church.DesignationId = (Guid)OReader["DesignationId"];
                                Church.Image = CommonFunction.GetOrginalImagePath(OReader["Image"].ToString(), IMAGEPATH.CHURCHIMAGE);
                                Church.OrganizationId = (Int32)OReader["OrganizationId"];
                                Church.TotalRecords = (Int32)OReader["TotalRecords"];
                                Church.ChurchContact = new EntityChurchContact();
                                Church.ChurchContact.CountryId = (Guid)OReader["CountryId"];
                                Church.ChurchContact.StateId = (Guid)OReader["StateId"];
                                Church.ChurchContact.Addressline1 = OReader["Addressline1"].ToString();
                                Church.ChurchContact.Addressline2 = OReader["Addressline2"].ToString();
                                Church.ChurchContact.Pincode = OReader["Pincode"].ToString();
                                Church.OrganizationPartition = new EnitityOrganizationPartition();
                                Church.OrganizationPartition.PartitionName = OReader["PartitionName"].ToString();
                                Church.OrganizationPartition.PartitionAbrivation = OReader["PartitionAbrivation"].ToString();
                                Church.OrganizationPartition.OrganizationLevel = (Int32)OReader["OrganizationLevel"];
                                Church.OrganizationPartition.ParentPartitionId = (Guid)OReader["ParentPartitionId"];
                                Church.Designation = new EntityChurchDesignation();
                                Church.Designation.DesignationAbrivation = OReader["DesignationAbrivation"].ToString();
                                Church.Designation.DesignationName = OReader["DesignationName"].ToString();
                                Churches.Add(Church);
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return Churches;
        }
        public List<EntityChurch> GetChurch()
        {
            List<EntityChurch> Churches = new List<EntityChurch>();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_Church]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, GETMODE.ALL);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return Churches;
                            }
                            while (OReader.Read())
                            {
                                EntityChurch Church = new EntityChurch();
                                Church.ChurchId = (Guid)OReader["ChurchId"];
                                Church.ChurchNo = (Int32)OReader["ChurchNo"];
                                Church.ChurchName = OReader["ChurchName"].ToString();
                                Church.IsLoginCreated = (bool)OReader["IsLoginCreated"];
                                Church.Email = OReader["Email"].ToString();
                                Church.ContactNo = OReader["ContactNo"].ToString();
                                Church.GradeId = (Guid)OReader["GradeId"];
                                Church.PartitionId = (Guid)OReader["PartitionId"];
                                Church.DesignationId = (Guid)OReader["DesignationId"];
                                Church.Image = CommonFunction.GetOrginalImagePath(OReader["Image"].ToString(), IMAGEPATH.CHURCHIMAGE);
                                Church.OrganizationId = (Int32)OReader["OrganizationId"];
                                Church.TotalRecords = (Int32)OReader["TotalRecords"];
                                Church.ChurchContact = new EntityChurchContact();
                                Church.ChurchContact.CountryId = (Guid)OReader["CountryId"];
                                Church.ChurchContact.StateId = (Guid)OReader["StateId"];
                                Church.ChurchContact.Addressline1 = OReader["Addressline1"].ToString();
                                Church.ChurchContact.Addressline2 = OReader["Addressline2"].ToString();
                                Church.ChurchContact.Pincode = OReader["Pincode"].ToString();
                                Church.OrganizationPartition = new EnitityOrganizationPartition();
                                Church.OrganizationPartition.PartitionName = OReader["PartitionName"].ToString();
                                Church.OrganizationPartition.PartitionAbrivation = OReader["PartitionAbrivation"].ToString();
                                Church.OrganizationPartition.OrganizationLevel = (Int32)OReader["OrganizationLevel"];
                                Church.OrganizationPartition.ParentPartitionId = (Guid)OReader["ParentPartitionId"];
                                Church.Designation = new EntityChurchDesignation();
                                Church.Designation.DesignationAbrivation = OReader["DesignationAbrivation"].ToString();
                                Church.Designation.DesignationName = OReader["DesignationName"].ToString();
                                Churches.Add(Church);
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return Churches;
        }
        public EntityChurch GetChurch(Guid ChurchId)
        {
            EntityChurch Church = new EntityChurch();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_Church]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, GETMODE.ONE);
                    SQLServer.AddInParameter(dbCommand, "@ChurchId", DbType.Guid, ChurchId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return Church;
                            }
                            while (OReader.Read())
                            {
                                Church.ChurchId = (Guid)OReader["ChurchId"];
                                Church.ChurchNo = (Int32)OReader["ChurchNo"];
                                Church.ChurchName = OReader["ChurchName"].ToString();
                                Church.IsLoginCreated = (bool)OReader["IsLoginCreated"];
                                Church.Email = OReader["Email"].ToString();
                                Church.ContactNo = OReader["ContactNo"].ToString();
                                Church.GradeId = (Guid)OReader["GradeId"];
                                Church.PartitionId = (Guid)OReader["PartitionId"];
                                Church.DesignationId = (Guid)OReader["DesignationId"];
                                Church.Image = CommonFunction.GetOrginalImagePath(OReader["Image"].ToString(), IMAGEPATH.CHURCHIMAGE);
                                Church.OrganizationId = (Int32)OReader["OrganizationId"];
                                Church.ChurchContact = new EntityChurchContact();
                                Church.ChurchContact.CountryId = (Guid)OReader["CountryId"];
                                Church.ChurchContact.StateId = (Guid)OReader["StateId"];
                                Church.ChurchContact.Addressline1 = OReader["Addressline1"].ToString();
                                Church.ChurchContact.Addressline2 = OReader["Addressline2"].ToString();
                                Church.ChurchContact.Pincode = OReader["Pincode"].ToString();
                                Church.OrganizationPartition = new EnitityOrganizationPartition();
                                Church.OrganizationPartition.PartitionName = OReader["PartitionName"].ToString();
                                Church.OrganizationPartition.PartitionAbrivation = OReader["PartitionAbrivation"].ToString();
                                Church.OrganizationPartition.OrganizationLevel = (Int32)OReader["OrganizationLevel"];
                                Church.OrganizationPartition.ParentPartitionId = (Guid)OReader["ParentPartitionId"];
                                Church.Designation = new EntityChurchDesignation();
                                Church.Designation.DesignationAbrivation = OReader["DesignationAbrivation"].ToString();
                                Church.Designation.DesignationName = OReader["DesignationName"].ToString();
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return Church;
        }
        public EntityChurchSammary GetChurchSummary()
        {
            EntityChurchSammary ChurchSammary = new EntityChurchSammary();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_ChurchSammary]"))
                {
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return ChurchSammary;
                            }
                            while (OReader.Read())
                            {
                                ChurchSammary.TotalChurches = (int)OReader["totalChurches"];
                                ChurchSammary.TotalInActiveChurches = (int)OReader["totalActiveChurches"];
                                ChurchSammary.TotalActiveChurchs = (int)OReader["totalDeletedChurches"];
                                ChurchSammary.TotalLoginCreatedChurches = (int)OReader["totalLoginChurches"];
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return ChurchSammary;
        }
        public string InsertChurchDetails(EntityChurch Church)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_Church]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.INS);
                    SQLServer.AddInParameter(dbCommand, "@OrganizationId", DbType.Int32, Church.OrganizationId);
                    SQLServer.AddInParameter(dbCommand, "@ChurchName", DbType.String, Church.ChurchName);
                    SQLServer.AddInParameter(dbCommand, "@Image", DbType.String, CommonFunction.UploadImage(IMAGEPATH.CHURCHIMAGE, Church.Image));
                    SQLServer.AddInParameter(dbCommand, "@Grade", DbType.Guid, Church.GradeId);
                    SQLServer.AddInParameter(dbCommand, "@IsLogin", DbType.Boolean, Church.IsLoginCreated);
                    SQLServer.AddInParameter(dbCommand, "@By", DbType.Guid, Current.Userid);
                    SQLServer.AddInParameter(dbCommand, "@On", DbType.DateTime, DateTime.Now);
                    SQLServer.AddInParameter(dbCommand, "@partitionID", DbType.Guid, Church.PartitionId);
                    SQLServer.AddInParameter(dbCommand, "@Designation", DbType.Guid, Church.DesignationId);
                    SQLServer.AddInParameter(dbCommand, "@Email", DbType.String, Church.Email);
                    SQLServer.AddInParameter(dbCommand, "@ContactNo", DbType.String, Church.ContactNo);
                    SQLServer.AddInParameter(dbCommand, "@CountryId", DbType.Guid, Church.ChurchContact.CountryId);
                    SQLServer.AddInParameter(dbCommand, "@StateId", DbType.Guid, Church.ChurchContact.StateId);
                    SQLServer.AddInParameter(dbCommand, "@AddressLine1", DbType.String, Church.ChurchContact.Addressline1);
                    SQLServer.AddInParameter(dbCommand, "@AddressLine2", DbType.String, Church.ChurchContact.Addressline2);
                    SQLServer.AddInParameter(dbCommand, "@PinCode", DbType.String, Church.ChurchContact.Pincode);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = SQLServer.ExecuteScalar(dbCommand).ToString();
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public string UpdateChurchDetails(EntityChurch Church)
        {
            string IResult = "SME";

            using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_Church]"))
            {
                SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.UPD);
                SQLServer.AddInParameter(dbCommand, "@Churchid", DbType.Guid, Church.ChurchId);
                SQLServer.AddInParameter(dbCommand, "@OrganizationId", DbType.Int32, Church.OrganizationId);
                SQLServer.AddInParameter(dbCommand, "@ChurchName", DbType.String, Church.ChurchName);
                SQLServer.AddInParameter(dbCommand, "@Image", DbType.String, CommonFunction.UploadImage(IMAGEPATH.CHURCHIMAGE, Church.Image));
                SQLServer.AddInParameter(dbCommand, "@Grade", DbType.Guid, Church.GradeId);
                SQLServer.AddInParameter(dbCommand, "@IsLogin", DbType.Boolean, Church.IsLoginCreated);
                SQLServer.AddInParameter(dbCommand, "@By", DbType.Guid, Current.Userid);
                SQLServer.AddInParameter(dbCommand, "@On", DbType.DateTime, DateTime.Now);
                SQLServer.AddInParameter(dbCommand, "@partitionID", DbType.Guid, Church.PartitionId);
                SQLServer.AddInParameter(dbCommand, "@Designation", DbType.Guid, Church.DesignationId);
                SQLServer.AddInParameter(dbCommand, "@Email", DbType.String, Church.Email);
                SQLServer.AddInParameter(dbCommand, "@ContactNo", DbType.String, Church.ContactNo);
                SQLServer.AddInParameter(dbCommand, "@CountryId", DbType.Guid, Church.ChurchContact.CountryId);
                SQLServer.AddInParameter(dbCommand, "@StateId", DbType.Guid, Church.ChurchContact.StateId);
                SQLServer.AddInParameter(dbCommand, "@AddressLine1", DbType.String, Church.ChurchContact.Addressline1);
                SQLServer.AddInParameter(dbCommand, "@AddressLine2", DbType.String, Church.ChurchContact.Addressline2);
                SQLServer.AddInParameter(dbCommand, "@PinCode", DbType.String, Church.ChurchContact.Pincode);
                using (DbConnection con = SQLServer.CreateConnection())
                {
                    IResult = (string)SQLServer.ExecuteScalar(dbCommand);
                }
            }
                   
            return IResult;
        }
        public string DeleteChurch(Guid ChurchId)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_Church]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.DEL);
                    SQLServer.AddInParameter(dbCommand, "@ChurchId", DbType.Guid, ChurchId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = (string)SQLServer.ExecuteScalar(dbCommand);
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public List<EntityChurchReport> GetChurchReport()
        {
            List<EntityChurchReport> churches = new List<EntityChurchReport>();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_Church]"))
                {
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return churches;
                            }
                            while (OReader.Read())
                            {
                                EntityChurchReport church = new EntityChurchReport();
                                church.CHChurchid = (Guid)OReader["CHChurchid"];
                                church.Addressline1 = OReader["Addressline1"].ToString();
                                church.Addressline2 = OReader["Addressline2"].ToString();
                                church.ChurchName = OReader["ChurchName"].ToString();
                                church.ContactNo = OReader["ContactNo"].ToString();
                                church.CountryName = OReader["CountryName"].ToString();
                                church.DesignationName = OReader["DesignationName"].ToString();
                                church.Email = OReader["Email"].ToString();
                                church.GradeName = OReader["GradeName"].ToString();
                                church.PartitionName = OReader["PartitionName"].ToString();
                                church.Pincode = OReader["Pincode"].ToString();
                                church.StateName = OReader["StateName"].ToString();
                                churches.Add(church);
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return churches;
        }
        public List<EntityChurch> GetChurches()
        {
            List<EntityChurch> churches = new List<EntityChurch>();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_ChurchDetail]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.LIST);
                    SQLServer.AddInParameter(dbCommand, "@Churchid", DbType.Guid, null);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return churches;
                            }
                            while (OReader.Read())
                            {
                                EntityChurch church = new EntityChurch();
                                //church.CHChurchid = (Guid)OReader["CHChurchid"];
                                //church.ChurchId = OReader["ChurchId"].ToString();
                                church.ChurchName = OReader["ChurchName"].ToString();
                                church.Email = OReader["Email"].ToString();
                                church.Image = OReader["Image"].ToString();
                                church.OrganizationId = (Int32)OReader["OrganizationId"];
                                church.IsLoginCreated = (Boolean)OReader["IsLoginCreated"];
                                church.ChurchContact = new EntityChurchContact();
                                //church.ChurchContact.ContactNo = OReader["ContactNo"].ToString();
                                church.ChurchContact.CountryId = (Guid)OReader["CountryId"];
                                church.ChurchContact.StateId = (Guid)OReader["StateId"];
                                church.ChurchContact.Addressline1 = OReader["Addressline1"].ToString();
                                church.ChurchContact.Addressline2 = OReader["Addressline2"].ToString();
                                church.ChurchContact.Pincode = OReader["Pincode"].ToString();
                                church.Grade = new EntityGrade();
                                church.Grade.GradeId = (Guid)OReader["GradeId"];
                                church.Grade.GradeName = OReader["GradeName"].ToString();
                                church.Designation = new EntityChurchDesignation();
                                church.Designation.DesignationId = (Guid)OReader["DesignationId"];
                                church.Designation.DesignationName = OReader["DesignationName"].ToString();
                                church.Designation.DesignationAbrivation = OReader["DesignationAbrivation"].ToString();
                                church.OrganizationPartition = new EnitityOrganizationPartition();
                                church.OrganizationPartition.PartitionId = (Guid)OReader["PartitionId"];
                                church.OrganizationPartition.PartitionName = OReader["PartitionName"].ToString();
                                church.OrganizationPartition.PartitionAbrivation = OReader["PartitionAbrivation"].ToString();
                                church.ChurchContact.Country = new TableEntity.EntityCountry();
                                church.ChurchContact.Country.CountryName = OReader["CountryName"].ToString();
                                church.ChurchContact.State = new EntityStates();
                                church.ChurchContact.State.StateName = OReader["StateName"].ToString();
                                churches.Add(church);
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return churches;
        }
        public List<EntityChurch> GetChurches(Guid Partitionid)
        {
            List<EntityChurch> churches = new List<EntityChurch>();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_ChurchDetail]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, GETMODE.SEARCH);
                    SQLServer.AddInParameter(dbCommand, "@Partitionid", DbType.Guid, Partitionid);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return churches;
                            }
                            while (OReader.Read())
                            {
                                EntityChurch church = new EntityChurch();
                                //church.CHChurchid = (Guid)OReader["CHChurchid"];
                                //church.ChurchId = OReader["ChurchId"].ToString();
                                church.ChurchName = OReader["ChurchName"].ToString();
                                church.OrganizationId = (Int32)OReader["OrganizationId"];
                                church.Email = OReader["Email"].ToString();
                                church.ChurchContact = new EntityChurchContact();
                                //church.ChurchContact.ContactNo = OReader["ChurchName"].ToString();
                                church.Grade = new EntityGrade();
                                church.Grade.GradeId = (Guid)OReader["GradeId"];
                                church.Grade.GradeName = OReader["GradeName"].ToString();
                                church.Designation = new EntityChurchDesignation();
                                church.Designation.DesignationId = (Guid)OReader["DesignationId"];
                                church.Designation.DesignationName = OReader["DesignationName"].ToString();
                                church.OrganizationPartition = new EnitityOrganizationPartition();
                                church.OrganizationPartition.PartitionId = (Guid)OReader["PartitionId"];
                                church.OrganizationPartition.PartitionName = OReader["PartitionName"].ToString();
                                churches.Add(church);
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return churches;
        }
        public List<EntityChurchDesignation> GetChurchDesignation()
        {
            List<EntityChurchDesignation> ChurchDesignations = new List<EntityChurchDesignation>();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_ChurchDesignation]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@mode", DbType.String, eMode.LIST);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return ChurchDesignations;
                            }
                            while (OReader.Read())
                            {
                                EntityChurchDesignation ChurchDesignation = new EntityChurchDesignation();
                                ChurchDesignation.DesignationId = (Guid)OReader["DesignationId"];
                                ChurchDesignation.DesignationName = OReader["DesignationName"].ToString();
                                ChurchDesignations.Add(ChurchDesignation);
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return ChurchDesignations;
        }
        #region Designation
        public string InsertDesignation(EntityChurchDesignation Designation)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_Designation]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.INS);
                    SQLServer.AddInParameter(dbCommand, "@DesignationName", DbType.String, Designation.DesignationName);
                    SQLServer.AddInParameter(dbCommand, "@OrganizationId", DbType.String, Designation.OrganizationId);
                    SQLServer.AddInParameter(dbCommand, "@DesignationAbrivation", DbType.String, Designation.DesignationAbrivation);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = SQLServer.ExecuteScalar(dbCommand).ToString();
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public string UpdateDesignation(EntityChurchDesignation Designation)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_Designation]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.UPD);
                    SQLServer.AddInParameter(dbCommand, "@DesignationId", DbType.Guid, Designation.DesignationId);
                    SQLServer.AddInParameter(dbCommand, "@DesignationName", DbType.String, Designation.DesignationName);
                    SQLServer.AddInParameter(dbCommand, "@OrganizationId", DbType.String, Designation.OrganizationId);
                    SQLServer.AddInParameter(dbCommand, "@DesignationAbrivation", DbType.String, Designation.DesignationAbrivation);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = SQLServer.ExecuteScalar(dbCommand).ToString();
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public string DeleteDesignation(Guid DesignationId)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_Designation]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.DEL);
                    SQLServer.AddInParameter(dbCommand, "@DesignationId", DbType.Guid, DesignationId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = SQLServer.ExecuteScalar(dbCommand).ToString();
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public List<EntityChurchDesignation> GetDesignations()
        {
            List<EntityChurchDesignation> Designations = new List<EntityChurchDesignation>();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_Designation]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, GETMODE.LIST);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return Designations;
                            }
                            while (OReader.Read())
                            {
                                EntityChurchDesignation ChurchDesignation = new EntityChurchDesignation();
                                ChurchDesignation.DesignationId = (Guid)OReader["DesignationId"];
                                ChurchDesignation.DesignationName = OReader["DesignationName"].ToString();
                                ChurchDesignation.OrganizationId = (int)OReader["OrganizationId"];
                                ChurchDesignation.OrganizationName = OReader["OrganizationName"].ToString();
                                ChurchDesignation.DesignationAbrivation = OReader["DesignationAbrivation"].ToString();
                                Designations.Add(ChurchDesignation);
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return Designations;
        }
        public List<EntityChurchDesignation> GetDesignations(int OrganizationId)
        {
            List<EntityChurchDesignation> Designations = new List<EntityChurchDesignation>();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_Designation]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, GETMODE.SEARCH);
                    SQLServer.AddInParameter(dbCommand, "@OrganizationId", DbType.Int16, OrganizationId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return Designations;
                            }
                            while (OReader.Read())
                            {
                                EntityChurchDesignation ChurchDesignation = new EntityChurchDesignation();
                                ChurchDesignation.DesignationId = (Guid)OReader["DesignationId"];
                                ChurchDesignation.DesignationName = OReader["DesignationName"].ToString();
                                ChurchDesignation.OrganizationId = (int)OReader["OrganizationId"];
                                ChurchDesignation.OrganizationName = OReader["OrganizationName"].ToString();
                                ChurchDesignation.DesignationAbrivation = OReader["DesignationAbrivation"].ToString();
                                Designations.Add(ChurchDesignation);
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return Designations;
        }
        public EntityChurchDesignation GetDesignation(Guid DesignationId)
        {
            EntityChurchDesignation ChurchDesignation = new EntityChurchDesignation();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_Designation]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, GETMODE.ONE);
                    SQLServer.AddInParameter(dbCommand, "@DesignationId", DbType.Guid, DesignationId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return ChurchDesignation;
                            }
                            while (OReader.Read())
                            {
                                ChurchDesignation.DesignationId = (Guid)OReader["DesignationId"];
                                ChurchDesignation.DesignationName = OReader["DesignationName"].ToString();
                                ChurchDesignation.OrganizationId = (int)OReader["OrganizationId"];
                                ChurchDesignation.OrganizationName = OReader["OrganizationName"].ToString();
                                ChurchDesignation.DesignationAbrivation = OReader["DesignationAbrivation"].ToString();
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return ChurchDesignation;
        }
        #endregion
        #region ChurchAsserts
        public string InsertChurchAsserts(EntityChurchAsserts ChurchAsserts)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_ChurchAssert]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.INS);
                    SQLServer.AddInParameter(dbCommand, "@ChurchId", DbType.Guid, Current.ChurchId);
                    SQLServer.AddInParameter(dbCommand, "@DateOfAssert", DbType.String, ChurchAsserts.DateOfAsserts);
                    SQLServer.AddInParameter(dbCommand, "@NameOfAssert", DbType.String, ChurchAsserts.NameOfAsserts);
                    SQLServer.AddInParameter(dbCommand, "@TotalNumber", DbType.Int32, ChurchAsserts.TotalNumbers);
                    SQLServer.AddInParameter(dbCommand, "@PriceAmount", DbType.String, ChurchAsserts.PriceAmount);
                    SQLServer.AddInParameter(dbCommand, "@Remarks", DbType.String, ChurchAsserts.Remarks);
                    SQLServer.AddInParameter(dbCommand, "@On", DbType.DateTime, DateTime.Now);
                    SQLServer.AddInParameter(dbCommand, "@By", DbType.Guid, Current.Userid);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = SQLServer.ExecuteScalar(dbCommand).ToString();
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public string UpdateChurchAsserts(EntityChurchAsserts ChurchAsserts)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_ChurchAssert]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.UPD);
                    SQLServer.AddInParameter(dbCommand, "@ChurchId", DbType.Guid, Current.ChurchId);
                    SQLServer.AddInParameter(dbCommand, "@ChurchAssertid", DbType.Guid, ChurchAsserts.ChurchAssertsId);
                    SQLServer.AddInParameter(dbCommand, "@DateOfAssert", DbType.String, ChurchAsserts.DateOfAsserts);
                    SQLServer.AddInParameter(dbCommand, "@NameOfAssert", DbType.String, ChurchAsserts.NameOfAsserts);
                    SQLServer.AddInParameter(dbCommand, "@TotalNumber", DbType.Int32, ChurchAsserts.TotalNumbers);
                    SQLServer.AddInParameter(dbCommand, "@PriceAmount", DbType.String, ChurchAsserts.PriceAmount);
                    SQLServer.AddInParameter(dbCommand, "@Remarks", DbType.String, ChurchAsserts.Remarks);
                    SQLServer.AddInParameter(dbCommand, "@On", DbType.DateTime, DateTime.Now);
                    SQLServer.AddInParameter(dbCommand, "@By", DbType.Guid, ChurchAsserts.ChurchAssertsId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = SQLServer.ExecuteScalar(dbCommand).ToString();
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public string DeleteChurchAsserts(Guid AssertId)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_ChurchAssert]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.DEL);
                    SQLServer.AddInParameter(dbCommand, "@ChurchAssertid", DbType.Guid, AssertId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = SQLServer.ExecuteScalar(dbCommand).ToString();
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public List<EntityChurchAsserts> GetChurchAsserts()
        {
            List<EntityChurchAsserts> ChurchAsserts = new List<EntityChurchAsserts>();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_ChurchAsserts]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, GETMODE.LIST);
                    SQLServer.AddInParameter(dbCommand, "@Churchid", DbType.Guid, Current.ChurchId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return ChurchAsserts;
                            }
                            while (OReader.Read())
                            {
                                EntityChurchAsserts ChurchAssert = new EntityChurchAsserts();
                                ChurchAssert.CHChurchId = (Guid)OReader["CHChurchId"];
                                ChurchAssert.ChurchAssertsId = (Guid)OReader["ChurchAssertsId"];
                                //ChurchAssert.DateOfAsserts = CommonFunction.ConvertDate(OReader["DateOfAsserts"].ToString());
                                ChurchAssert.NameOfAsserts = OReader["NameOfAsserts"].ToString();
                                ChurchAssert.PriceAmount = OReader["PriceAmount"].ToString();
                                ChurchAssert.Remarks = OReader["Remarks"].ToString();
                                ChurchAssert.TotalNumbers = OReader["TotalNumbers"].ToString();
                                ChurchAsserts.Add(ChurchAssert);
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return ChurchAsserts;
        }
        public EntityChurchAsserts GetChurchAsserts(Guid AssertId)
        {
            EntityChurchAsserts ChurchAssert = new EntityChurchAsserts();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_ChurchAsserts]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, GETMODE.ONE);
                    SQLServer.AddInParameter(dbCommand, "@AssertId", DbType.Guid, AssertId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return ChurchAssert;
                            }
                            while (OReader.Read())
                            {
                                ChurchAssert.ChurchAssertsId = (Guid)OReader["ChurchAssertsId"];
                                //ChurchAssert.DateOfAsserts = CommonFunction.ConvertDate(OReader["DateOfAsserts"].ToString());
                                ChurchAssert.NameOfAsserts = OReader["NameOfAsserts"].ToString();
                                ChurchAssert.PriceAmount = OReader["PriceAmount"].ToString();
                                ChurchAssert.Remarks = OReader["Remarks"].ToString();
                                ChurchAssert.TotalNumbers = OReader["TotalNumbers"].ToString();
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return ChurchAssert;
        }
        #endregion
        #region ChurchBoard
        public string InsertChurchBoard(EntityChurchBoard ChurchBoard)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_ChurchBoard]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.INS);
                    SQLServer.AddInParameter(dbCommand, "@BoardName", DbType.String, ChurchBoard.BoardName);
                    SQLServer.AddInParameter(dbCommand, "@BoardAbbrivation", DbType.String, ChurchBoard.BoardAbbrivation);
                    SQLServer.AddInParameter(dbCommand, "@By", DbType.Guid, Current.Userid);
                    SQLServer.AddInParameter(dbCommand, "@On", DbType.DateTime, DateTime.Now);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = SQLServer.ExecuteScalar(dbCommand).ToString();
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public string UpdateChurchBoard(EntityChurchBoard ChurchBoard)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_ChurchBoard]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.UPD);
                    SQLServer.AddInParameter(dbCommand, "@BoardId", DbType.Guid, ChurchBoard.BoardId);
                    SQLServer.AddInParameter(dbCommand, "@BoardName", DbType.String, ChurchBoard.BoardName);
                    SQLServer.AddInParameter(dbCommand, "@BoardAbbrivation", DbType.String, ChurchBoard.BoardAbbrivation);
                    SQLServer.AddInParameter(dbCommand, "@By", DbType.Guid, Current.Userid);
                    SQLServer.AddInParameter(dbCommand, "@On", DbType.DateTime, DateTime.Now);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = SQLServer.ExecuteScalar(dbCommand).ToString();
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public string DeleteChurchBoard(Guid BoardId)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_ChurchBoard]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.DEL);
                    SQLServer.AddInParameter(dbCommand, "@BoardId", DbType.Guid, BoardId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = SQLServer.ExecuteScalar(dbCommand).ToString();
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public List<EntityChurchBoard> GetChurchBoard()
        {
            List<EntityChurchBoard> ChurchBoards = new List<EntityChurchBoard>();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_ChurchBoard]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, GETMODE.LIST);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return ChurchBoards;
                            }
                            while (OReader.Read())
                            {
                                EntityChurchBoard ChurchBoard = new EntityChurchBoard();
                                ChurchBoard.BoardId = (Guid)OReader["BoardId"];
                                ChurchBoard.BoardName = OReader["BoardName"].ToString();
                                ChurchBoard.BoardAbbrivation = OReader["BoardAbbrivation"].ToString();
                                ChurchBoards.Add(ChurchBoard);
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return ChurchBoards;
        }
        public EntityChurchBoard GetChurchBoard(Guid BoardId)
        {
            EntityChurchBoard ChurchBoard = new EntityChurchBoard();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_ChurchBoard]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, GETMODE.ONE);
                    SQLServer.AddInParameter(dbCommand, "@BoardId", DbType.Guid, BoardId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return ChurchBoard;
                            }
                            while (OReader.Read())
                            {
                                ChurchBoard.BoardId = (Guid)OReader["BoardId"];
                                ChurchBoard.BoardName = OReader["BoardName"].ToString();
                                ChurchBoard.BoardAbbrivation = OReader["BoardAbbrivation"].ToString();
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return ChurchBoard;
        }
        #endregion
        #region ChurchBoardJobtitle
        public string InsertChurchBoardJobtitle(EntityChurchBoardJobtitle ChurchBoardJobtitle)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_ChurchBoardJobtitle]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.INS);
                    SQLServer.AddInParameter(dbCommand, "@BoardId", DbType.Guid, ChurchBoardJobtitle.BoardId);
                    SQLServer.AddInParameter(dbCommand, "@JobtitleName", DbType.String, ChurchBoardJobtitle.JobtitleName);
                    SQLServer.AddInParameter(dbCommand, "@CanHaveMultiple", DbType.Boolean, ChurchBoardJobtitle.CanHaveMultiple);
                    SQLServer.AddInParameter(dbCommand, "@By", DbType.Guid, Current.Userid);
                    SQLServer.AddInParameter(dbCommand, "@On", DbType.DateTime, DateTime.Now);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = SQLServer.ExecuteScalar(dbCommand).ToString();
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public string UpdateChurchBoardJobtitle(EntityChurchBoardJobtitle ChurchBoardJobtitle)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_ChurchBoardJobtitle]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.UPD);
                    SQLServer.AddInParameter(dbCommand, "@BoardId", DbType.Guid, ChurchBoardJobtitle.BoardId);
                    SQLServer.AddInParameter(dbCommand, "@BoardJobtitleId", DbType.Guid, ChurchBoardJobtitle.BoardJobtitleId);
                    SQLServer.AddInParameter(dbCommand, "@JobtitleName", DbType.String, ChurchBoardJobtitle.JobtitleName);
                    SQLServer.AddInParameter(dbCommand, "@CanHaveMultiple", DbType.Boolean, ChurchBoardJobtitle.CanHaveMultiple);
                    SQLServer.AddInParameter(dbCommand, "@By", DbType.Guid, Current.Userid);
                    SQLServer.AddInParameter(dbCommand, "@On", DbType.DateTime, DateTime.Now);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = SQLServer.ExecuteScalar(dbCommand).ToString();
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public string DeleteChurchBoardJobtitle(Guid BoardJobtitleId)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_ChurchBoardJobtitle]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.DEL);
                    SQLServer.AddInParameter(dbCommand, "@BoardJobtitleId", DbType.Guid, BoardJobtitleId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = SQLServer.ExecuteScalar(dbCommand).ToString();
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public List<EntityChurchBoardJobtitle> GetChurchBoardJobtitle()
        {
            List<EntityChurchBoardJobtitle> ChurchBoardJobtitles = new List<EntityChurchBoardJobtitle>();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_ChurchBoardJobtitle]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, GETMODE.LIST);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return ChurchBoardJobtitles;
                            }
                            while (OReader.Read())
                            {
                                EntityChurchBoardJobtitle ChurchBoardJobtitle = new EntityChurchBoardJobtitle();
                                ChurchBoardJobtitle.BoardId = (Guid)OReader["BoardId"];
                                ChurchBoardJobtitle.BoardJobtitleId = (Guid)OReader["BoardJobtitleId"];
                                ChurchBoardJobtitle.JobtitleName = OReader["JobtitleName"].ToString();
                                ChurchBoardJobtitle.CanHaveMultiple = (Boolean)OReader["CanHaveMultiple"];
                                ChurchBoardJobtitle.ChurchBoard = new EntityChurchBoard();
                                ChurchBoardJobtitle.ChurchBoard.BoardName = OReader["BoardName"].ToString();

                                ChurchBoardJobtitles.Add(ChurchBoardJobtitle);
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return ChurchBoardJobtitles;
        }
        public List<EntityChurchBoardJobtitle> GetChurchBoardJobtitlebyBoardId(Guid BoardId)
        {
            List<EntityChurchBoardJobtitle> ChurchBoardJobtitles = new List<EntityChurchBoardJobtitle>();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_ChurchBoardJobtitle]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, GETMODE.SEARCH);
                    SQLServer.AddInParameter(dbCommand, "@BoardId", DbType.Guid, BoardId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return ChurchBoardJobtitles;
                            }
                            while (OReader.Read())
                            {
                                EntityChurchBoardJobtitle ChurchBoardJobtitle = new EntityChurchBoardJobtitle();
                                ChurchBoardJobtitle.BoardId = (Guid)OReader["BoardId"];
                                ChurchBoardJobtitle.BoardJobtitleId = (Guid)OReader["BoardJobtitleId"];
                                ChurchBoardJobtitle.JobtitleName = OReader["JobtitleName"].ToString();
                                ChurchBoardJobtitle.CanHaveMultiple = (Boolean)OReader["CanHaveMultiple"];
                                ChurchBoardJobtitle.ChurchBoard = new EntityChurchBoard();
                                ChurchBoardJobtitle.ChurchBoard.BoardName = OReader["BoardName"].ToString();
                                ChurchBoardJobtitles.Add(ChurchBoardJobtitle);
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return ChurchBoardJobtitles;
        }

        public List<EntityChurchBoardJobtitle> GetChurchBoardAssignedJobtitle(Guid BoardId)
        {
            List<EntityChurchBoardJobtitle> ChurchBoardJobtitles = new List<EntityChurchBoardJobtitle>();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_ChurchBoardJobtitle]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, "ASSIGNED");
                    SQLServer.AddInParameter(dbCommand, "@BoardId", DbType.Guid, BoardId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return ChurchBoardJobtitles;
                            }
                            while (OReader.Read())
                            {
                                EntityChurchBoardJobtitle ChurchBoardJobtitle = new EntityChurchBoardJobtitle();
                                ChurchBoardJobtitle.BoardId = (Guid)OReader["BoardId"];
                                ChurchBoardJobtitle.BoardJobtitleId = (Guid)OReader["BoardJobtitleId"];
                                ChurchBoardJobtitle.JobtitleName = OReader["JobtitleName"].ToString();
                                ChurchBoardJobtitle.CanHaveMultiple = (Boolean)OReader["CanHaveMultiple"];
                                ChurchBoardJobtitle.ChurchBoard = new EntityChurchBoard();
                                ChurchBoardJobtitle.ChurchBoard.BoardName = OReader["BoardName"].ToString();
                                ChurchBoardJobtitles.Add(ChurchBoardJobtitle);
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return ChurchBoardJobtitles;
        }
        public List<EntityChurchBoardJobtitle> GetChurchBoardJobtitlebyRole(Guid BoardId)
        {
            List<EntityChurchBoardJobtitle> ChurchBoardJobtitles = new List<EntityChurchBoardJobtitle>();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_ChurchBoardJobtitle]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, "BYJOBROLE");
                    SQLServer.AddInParameter(dbCommand, "@BoardId", DbType.Guid, BoardId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return ChurchBoardJobtitles;
                            }
                            while (OReader.Read())
                            {
                                EntityChurchBoardJobtitle ChurchBoardJobtitle = new EntityChurchBoardJobtitle();
                                ChurchBoardJobtitle.BoardId = (Guid)OReader["BoardId"];
                                ChurchBoardJobtitle.BoardJobtitleId = (Guid)OReader["BoardJobtitleId"];
                                ChurchBoardJobtitle.CanHaveMultiple = (Boolean)OReader["CanHaveMultiple"];
                                ChurchBoardJobtitle.JobtitleName = OReader["JobtitleName"].ToString();
                                ChurchBoardJobtitle.ChurchBoard = new EntityChurchBoard();
                                ChurchBoardJobtitle.ChurchBoard.BoardName = OReader["BoardName"].ToString();
                                ChurchBoardJobtitles.Add(ChurchBoardJobtitle);
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return ChurchBoardJobtitles;
        }
        public EntityChurchBoardJobtitle GetChurchBoardJobtitle(Guid BoardJobtitleId)
        {
            EntityChurchBoardJobtitle ChurchBoardJobtitle = new EntityChurchBoardJobtitle();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_ChurchBoardJobtitle]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, GETMODE.ONE);
                    SQLServer.AddInParameter(dbCommand, "@BoardJobtitleId", DbType.Guid, BoardJobtitleId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return ChurchBoardJobtitle;
                            }
                            while (OReader.Read())
                            {
                                ChurchBoardJobtitle.BoardJobtitleId = (Guid)OReader["BoardJobtitleId"];
                                ChurchBoardJobtitle.BoardId = (Guid)OReader["BoardId"];
                                ChurchBoardJobtitle.CanHaveMultiple = (Boolean)OReader["CanHaveMultiple"];
                                ChurchBoardJobtitle.JobtitleName = OReader["JobtitleName"].ToString();
                                ChurchBoardJobtitle.ChurchBoard = new EntityChurchBoard();
                                ChurchBoardJobtitle.ChurchBoard.BoardName = OReader["BoardName"].ToString();
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return ChurchBoardJobtitle;
        }
        #endregion
        #region IncomeType
        public string InsertIncomeType(EntityIncomeType IncomeType)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_Incometype]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.INS);
                    SQLServer.AddInParameter(dbCommand, "@IncomeTypeName", DbType.String, IncomeType.IncomeTypeName);
                    SQLServer.AddInParameter(dbCommand, "@EnableIntialAmount", DbType.Boolean, IncomeType.EnableIntitalAmount);
                    SQLServer.AddInParameter(dbCommand, "@InitialAmount", DbType.Double, IncomeType.IntitalAmount);
                    SQLServer.AddInParameter(dbCommand, "@IncomeType", DbType.Int32, IncomeType.IncomeType);
                    SQLServer.AddInParameter(dbCommand, "@fromDate", DbType.String, IncomeType.fromDate);
                    SQLServer.AddInParameter(dbCommand, "@Todate", DbType.String, IncomeType.ToDate);
                    SQLServer.AddInParameter(dbCommand, "@EnableTarget", DbType.Boolean, IncomeType.EnableTargetAmount);
                    SQLServer.AddInParameter(dbCommand, "@TargetAmount", DbType.Double, IncomeType.TargetAmount);
                    SQLServer.AddInParameter(dbCommand, "@ApplyAmount", DbType.Boolean, IncomeType.Applyto);
                    SQLServer.AddInParameter(dbCommand, "@EnableBarrow", DbType.Boolean, IncomeType.EnableBarrow);
                    SQLServer.AddInParameter(dbCommand, "@By", DbType.Guid, Current.Userid);
                    SQLServer.AddInParameter(dbCommand, "@On", DbType.DateTime, DateTime.Now);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = SQLServer.ExecuteScalar(dbCommand).ToString();
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public string UpdateIncomeType(EntityIncomeType IncomeType)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_Incometype]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.UPD);
                    SQLServer.AddInParameter(dbCommand, "@IncomeTypeId", DbType.Guid, IncomeType.IncomeTypeId);
                    SQLServer.AddInParameter(dbCommand, "@IncomeTypeName", DbType.String, IncomeType.IncomeTypeName);
                    SQLServer.AddInParameter(dbCommand, "@EnableIntialAmount", DbType.Boolean, IncomeType.EnableIntitalAmount);
                    SQLServer.AddInParameter(dbCommand, "@InitialAmount", DbType.Double, IncomeType.IntitalAmount);
                    SQLServer.AddInParameter(dbCommand, "@IncomeType", DbType.Int32, IncomeType.IncomeType);
                    SQLServer.AddInParameter(dbCommand, "@fromDate", DbType.String, IncomeType.fromDate);
                    SQLServer.AddInParameter(dbCommand, "@Todate", DbType.String, IncomeType.ToDate);
                    SQLServer.AddInParameter(dbCommand, "@EnableTarget", DbType.Boolean, IncomeType.EnableTargetAmount);
                    SQLServer.AddInParameter(dbCommand, "@TargetAmount", DbType.Double, IncomeType.TargetAmount);
                    SQLServer.AddInParameter(dbCommand, "@ApplyAmount", DbType.Boolean, IncomeType.Applyto);
                    SQLServer.AddInParameter(dbCommand, "@EnableBarrow", DbType.Boolean, IncomeType.EnableBarrow);
                    SQLServer.AddInParameter(dbCommand, "@By", DbType.Guid, Current.Userid);
                    SQLServer.AddInParameter(dbCommand, "@On", DbType.DateTime, DateTime.Now);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = SQLServer.ExecuteScalar(dbCommand).ToString();
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public string DeleteIncomeType(Guid IncomeTypeId)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_Incometype]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.DEL);
                    SQLServer.AddInParameter(dbCommand, "@IncomeTypeId", DbType.Guid, IncomeTypeId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = SQLServer.ExecuteScalar(dbCommand).ToString();
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public List<EntityIncomeType> GetIncomeType()
        {
            List<EntityIncomeType> IncomeTypes = new List<EntityIncomeType>();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_IncomeType]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, GETMODE.LIST);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return IncomeTypes;
                            }
                            while (OReader.Read())
                            {
                                EntityIncomeType IncomeType = new EntityIncomeType();
                                IncomeType.IncomeTypeId = (Guid)OReader["IncomeTypeId"];
                                IncomeType.IncomeTypeName = OReader["IncomeTypeName"].ToString();
                                IncomeType.EnableIntitalAmount = (bool)OReader["EnableIntitalAmount"];
                                IncomeType.IntitalAmount = (double)OReader["IntitalAmount"];
                                IncomeType.IncomeType = (Int32)OReader["IncomeType"];
                                //IncomeType.fromDate = CommonFunction.ConvertDate(OReader["fromDate"].ToString());
                                //IncomeType.ToDate = CommonFunction.ConvertDate(OReader["ToDate"].ToString());
                                IncomeType.EnableTargetAmount = (bool)OReader["EnableTargetAmount"];
                                IncomeType.TargetAmount = (double)OReader["TargetAmount"];
                                IncomeType.Applyto = (Int32)OReader["Applyto"];
                                IncomeType.EnableBarrow = (bool)OReader["EnableBarrow"];
                                IncomeTypes.Add(IncomeType);
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return IncomeTypes;
        }
        public EntityIncomeType GetIncomeType(Guid IncomeTypeId)
        {
            EntityIncomeType IncomeType = new EntityIncomeType();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_IncomeType]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, GETMODE.ONE);
                    SQLServer.AddInParameter(dbCommand, "@Inometypeid", DbType.Guid, IncomeTypeId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return IncomeType;
                            }
                            while (OReader.Read())
                            {
                                IncomeType.IncomeTypeId = (Guid)OReader["IncomeTypeId"];
                                IncomeType.IncomeTypeName = OReader["IncomeTypeName"].ToString();
                                IncomeType.EnableIntitalAmount = (bool)OReader["EnableIntitalAmount"];
                                IncomeType.IntitalAmount = (double)OReader["IntitalAmount"];
                                IncomeType.IncomeType = (Int32)OReader["IncomeType"];
                                //IncomeType.fromDate = CommonFunction.ConvertDate(OReader["fromDate"].ToString());
                                //IncomeType.ToDate = CommonFunction.ConvertDate(OReader["ToDate"].ToString());
                                IncomeType.EnableTargetAmount = (bool)OReader["EnableTargetAmount"];
                                IncomeType.TargetAmount = (double)OReader["TargetAmount"];
                                IncomeType.Applyto = (Int32)OReader["Applyto"];
                                IncomeType.EnableBarrow = (bool)OReader["EnableBarrow"];
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return IncomeType;
        }
        #endregion
        #region Jobtitle Role
        public string InsertJobtitleRole(EntityChurchJobtitleRole JobtitleRole)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_JobtitleRole]"))
                {
                    
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.INS);
                    SQLServer.AddInParameter(dbCommand, "@BoardJobtitleId", DbType.Guid, JobtitleRole.BoardJobtitleId);
                    SQLServer.AddInParameter(dbCommand, "@MenuCategory", DbType.String, JobtitleRole.UserType.MenuCategory);
                    SQLServer.AddInParameter(dbCommand, "@MainMenus", DbType.String, JobtitleRole.UserType.MainMenu);
                    SQLServer.AddInParameter(dbCommand, "@Landinpage", DbType.Int32, JobtitleRole.LandingPage);
                    SQLServer.AddInParameter(dbCommand, "@SubMenus", DbType.String, JobtitleRole.UserType.SubMenu);
                    SQLServer.AddInParameter(dbCommand, "@By", DbType.Guid, Current.Userid);
                    SQLServer.AddInParameter(dbCommand, "@Status", DbType.Int32, JobtitleRole.status);
                    SQLServer.AddInParameter(dbCommand, "@On", DbType.DateTime, DateTime.Now);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = SQLServer.ExecuteScalar(dbCommand).ToString();
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public string UpdateJobtitleRole(EntityChurchJobtitleRole JobtitleRole)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_JobtitleRole]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.UPD);
                    SQLServer.AddInParameter(dbCommand, "@BoardJobtitleId", DbType.Guid, JobtitleRole.BoardJobtitleId);
                    SQLServer.AddInParameter(dbCommand, "@JobtitleRoleId", DbType.Guid, JobtitleRole.JobtitleRoleId);
                    SQLServer.AddInParameter(dbCommand, "@MenuCategory", DbType.String, JobtitleRole.UserType.MenuCategory);
                    SQLServer.AddInParameter(dbCommand, "@MainMenus", DbType.String, JobtitleRole.UserType.MainMenu);
                    SQLServer.AddInParameter(dbCommand, "@Landinpage", DbType.Int32, JobtitleRole.LandingPage);
                    SQLServer.AddInParameter(dbCommand, "@SubMenus", DbType.String, JobtitleRole.UserType.SubMenu);
                    SQLServer.AddInParameter(dbCommand, "@By", DbType.Guid, Current.Userid);
                    SQLServer.AddInParameter(dbCommand, "@Status", DbType.Int32, JobtitleRole.status);
                    SQLServer.AddInParameter(dbCommand, "@On", DbType.DateTime, DateTime.Now);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = SQLServer.ExecuteScalar(dbCommand).ToString();
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public string DeleteJobtitleRole(Guid JobtitleRoleId)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_JobtitleRole]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.DEL);
                    SQLServer.AddInParameter(dbCommand, "@JobtitleRoleId", DbType.Guid, JobtitleRoleId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = SQLServer.ExecuteScalar(dbCommand).ToString();
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public List<EntityChurchJobtitleRole> GetJobtitleRole()
        {
            List<EntityChurchJobtitleRole> JobtitleRoles = new List<EntityChurchJobtitleRole>();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_JobtitleRole]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, GETMODE.LIST);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return JobtitleRoles;
                            }
                            while (OReader.Read())
                            {
                                EntityChurchJobtitleRole JobtitleRole = new EntityChurchJobtitleRole();
                                JobtitleRole.JobtitleRoleId = (Guid)OReader["JobtitleRoleId"];
                                JobtitleRole.BoardJobtitleId = (Guid)OReader["BoardJobtitleId"];
                                JobtitleRole.status = (int)OReader["status"];
                                JobtitleRole.LandingPage = (int)OReader["landingPage"];
                                JobtitleRole.UserTypeId = (Guid)OReader["UserTypeId"];
                                JobtitleRole.BoardJobtitle = new EntityChurchBoardJobtitle();
                                JobtitleRole.BoardJobtitle.JobtitleName = OReader["JobtitleName"].ToString();
                                JobtitleRole.UserType = new EntityUserType();
                                JobtitleRole.UserType.SubMenu = OReader["SubMenu"].ToString();
                                JobtitleRole.UserType.MainMenu = OReader["MainMenu"].ToString();
                                JobtitleRole.UserType.MenuCategory = OReader["MenuCategory"].ToString();
                                JobtitleRole.BoardJobtitle.ChurchBoard = new EntityChurchBoard();
                                JobtitleRole.BoardJobtitle.ChurchBoard.BoardName = OReader["BoardName"].ToString();
                                JobtitleRole.BoardJobtitle.BoardId = (Guid)OReader["BoardId"];
                                JobtitleRoles.Add(JobtitleRole);
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return JobtitleRoles;
        }
        public EntityChurchJobtitleRole GetJobtitleRole(Guid JobtitleRoleId)
        {
            EntityChurchJobtitleRole JobtitleRole = new EntityChurchJobtitleRole();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_JobtitleRole]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, GETMODE.ONE);
                    SQLServer.AddInParameter(dbCommand, "@JobtitleRoleId", DbType.Guid, JobtitleRoleId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return JobtitleRole;
                            }
                            while (OReader.Read())
                            {
                                JobtitleRole.JobtitleRoleId = (Guid)OReader["JobtitleRoleId"];
                                JobtitleRole.BoardJobtitleId = (Guid)OReader["BoardJobtitleId"];
                                JobtitleRole.UserTypeId = (Guid)OReader["UserTypeId"];
                                JobtitleRole.status = (int)OReader["status"];
                                JobtitleRole.LandingPage = (int)OReader["landingPage"];
                                JobtitleRole.BoardJobtitle = new EntityChurchBoardJobtitle();
                                JobtitleRole.BoardJobtitle.JobtitleName = OReader["JobtitleName"].ToString();
                                JobtitleRole.UserType = new EntityUserType();
                                JobtitleRole.UserType.SubMenu = OReader["SubMenu"].ToString();
                                JobtitleRole.UserType.MainMenu = OReader["MainMenu"].ToString();
                                JobtitleRole.UserType.MenuCategory = OReader["MenuCategory"].ToString();
                                JobtitleRole.BoardJobtitle.ChurchBoard = new EntityChurchBoard();
                                JobtitleRole.BoardJobtitle.ChurchBoard.BoardName = OReader["BoardName"].ToString();
                                JobtitleRole.BoardJobtitle.BoardId = (Guid)OReader["BoardId"];
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return JobtitleRole;
        }

        #endregion
        #region PrayerGroup
        public string InsertPrayerGroup(EntityPrayerGroup PrayerGroup)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_PrayerGroup]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.INS);
                    SQLServer.AddInParameter(dbCommand, "@PrayerGroupId", DbType.Guid, PrayerGroup.PrayerGroupId);
                    SQLServer.AddInParameter(dbCommand, "@PrayerGroupTheme", DbType.Guid, PrayerGroup.PrayerGroupThemeId);
                    SQLServer.AddInParameter(dbCommand, "@PlaceOrNameOfPrayerGroup", DbType.String, PrayerGroup.PlaceOrNameOfPrayerGroup);
                    SQLServer.AddInParameter(dbCommand, "@PrayerGroupHead", DbType.Guid, PrayerGroup.PrayerGroupHead);
                    SQLServer.AddInParameter(dbCommand, "@PrayerGroupSchudle", DbType.String, PrayerGroup.PrayGroupShudleMode);
                    SQLServer.AddInParameter(dbCommand, "@StartTime", DbType.String, PrayerGroup.PrayerStartTime);
                    SQLServer.AddInParameter(dbCommand, "@Endtime", DbType.String, PrayerGroup.PrayerEndTime);
                    SQLServer.AddInParameter(dbCommand, "@PrayerDate", DbType.String, PrayerGroup.PrayerDate);
                    SQLServer.AddInParameter(dbCommand, "@PrayerDay", DbType.String, PrayerGroup.PrayerDay);
                    SQLServer.AddInParameter(dbCommand, "@PrayerGroupStatus", DbType.Int32, PrayerGroup.PrayGroupStatus);
                    SQLServer.AddInParameter(dbCommand, "@By", DbType.Guid, Current.Userid);
                    SQLServer.AddInParameter(dbCommand, "@On", DbType.DateTime, DateTime.Now);
                    SQLServer.AddInParameter(dbCommand, "@PrayerGroupMember", DbType.String, PrayerGroup.PrayerMembers);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = SQLServer.ExecuteScalar(dbCommand).ToString();
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public string UpdatePrayerGroup(EntityPrayerGroup PrayerGroup)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_PrayerGroup]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.UPD);
                    SQLServer.AddInParameter(dbCommand, "@PrayerGroupId", DbType.Guid, PrayerGroup.PrayerGroupId);
                    SQLServer.AddInParameter(dbCommand, "@PrayerGroupTheme", DbType.Guid, PrayerGroup.PrayerGroupThemeId);
                    SQLServer.AddInParameter(dbCommand, "@PlaceOrNameOfPrayerGroup", DbType.String, PrayerGroup.PlaceOrNameOfPrayerGroup);
                    SQLServer.AddInParameter(dbCommand, "@PrayerGroupHead", DbType.Guid, PrayerGroup.PrayerGroupHead);
                    SQLServer.AddInParameter(dbCommand, "@PrayerGroupSchudle", DbType.String, PrayerGroup.PrayGroupShudleMode);
                    SQLServer.AddInParameter(dbCommand, "@StartTime", DbType.String, PrayerGroup.PrayerStartTime);
                    SQLServer.AddInParameter(dbCommand, "@Endtime", DbType.String, PrayerGroup.PrayerEndTime);
                    SQLServer.AddInParameter(dbCommand, "@PrayerDate", DbType.String, PrayerGroup.PrayerDate);
                    SQLServer.AddInParameter(dbCommand, "@PrayerDay", DbType.String, PrayerGroup.PrayerDay);
                    SQLServer.AddInParameter(dbCommand, "@PrayerGroupStatus", DbType.Int32, PrayerGroup.PrayGroupStatus);
                    SQLServer.AddInParameter(dbCommand, "@By", DbType.Guid, Current.Userid);
                    SQLServer.AddInParameter(dbCommand, "@On", DbType.DateTime, DateTime.Now);
                    SQLServer.AddInParameter(dbCommand, "@PrayerGroupMember", DbType.String, PrayerGroup.PrayerMembers);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = SQLServer.ExecuteScalar(dbCommand).ToString();
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public string DeletePrayerGroup(Guid PrayerGroupId)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_PrayerGroup]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.DEL);
                    SQLServer.AddInParameter(dbCommand, "@PrayerGroupId", DbType.Guid, PrayerGroupId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = SQLServer.ExecuteScalar(dbCommand).ToString();
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public List<EntityPrayerGroup> GetPrayerGroup()
        {
            List<EntityPrayerGroup> PrayerGroups = new List<EntityPrayerGroup>();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_PrayerGroup]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, GETMODE.LIST);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return PrayerGroups;
                            }
                            while (OReader.Read())
                            {
                                EntityPrayerGroup PrayerGroup = new EntityPrayerGroup();
                                PrayerGroup.PlaceOrNameOfPrayerGroup = OReader["PlaceOrNameOfPrayerGroup"].ToString();
                                PrayerGroup.PrayerDate = OReader["PrayerDate"].ToString();
                                PrayerGroup.PrayerDay = OReader["PrayerDay"].ToString();
                                PrayerGroup.PrayerEndTime = OReader["PrayerEndTime"].ToString();
                                PrayerGroup.PrayerGroupHead = (Guid)OReader["PrayerGroupHead"];
                                PrayerGroup.PrayerGroupId = (Guid)OReader["PrayerGroupId"];
                                PrayerGroup.PrayerGroupThemeId = (Guid)OReader["PrayerGroupThemeId"];
                                PrayerGroup.PrayerStartTime = OReader["PrayerStartTime"].ToString();
                                PrayerGroup.PrayGroupShudleMode = OReader["PrayGroupShudleMode"].ToString();
                                PrayerGroup.PrayGroupStatus = (Int32)OReader["PrayGroupStatus"];
                                PrayerGroups.Add(PrayerGroup);
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return PrayerGroups;
        }
        public EntityPrayerGroup GetPrayerGroup(Guid PrayerGroupId)
        {
            EntityPrayerGroup PrayerGroup = new EntityPrayerGroup();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_PrayerGroup]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, GETMODE.ONE);
                    SQLServer.AddInParameter(dbCommand, "@PrayerGroupId", DbType.Guid, PrayerGroupId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return PrayerGroup;
                            }
                            while (OReader.Read())
                            {
                                PrayerGroup.PlaceOrNameOfPrayerGroup = OReader["PlaceOrNameOfPrayerGroup"].ToString();
                                PrayerGroup.PrayerDate = OReader["PrayerDate"].ToString();
                                PrayerGroup.PrayerDay = OReader["PrayerDay"].ToString();
                                PrayerGroup.PrayerEndTime = OReader["PrayerEndTime"].ToString();
                                PrayerGroup.PrayerGroupHead = (Guid)OReader["PrayerGroupHead"];
                                PrayerGroup.PrayerGroupId = (Guid)OReader["PrayerGroupId"];
                                PrayerGroup.PrayerGroupThemeId = (Guid)OReader["PrayerGroupThemeId"];
                                PrayerGroup.PrayerStartTime = OReader["PrayerStartTime"].ToString();
                                PrayerGroup.PrayGroupShudleMode = OReader["PrayGroupShudleMode"].ToString();
                                PrayerGroup.PrayGroupStatus = (Int32)OReader["PrayGroupStatus"];
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return PrayerGroup;
        }
        #endregion
        
    }
}